
USE master
go
CREATE DATABASE Graduate 
ON PRIMARY
(
    NAME='Graduate',
    FILENAME='D:\data\Graduate.mdf',
    SIZE=3MB,
    MAXSIZE=UNLIMITED,
    FILEGROWTH=1MB
)
LOG ON
(
    NAME='Graduate_log',
    FILENAME='D:\data\Graduate_log.ldf',
    SIZE=1MB,
    MAXSIZE=2097152MB,
    FILEGROWTH=10%
)
go
EXEC sp_dbcmptlevel 'Graduate',90
go
USE Graduate
go
EXEC sp_dboption 'Graduate','auto create statistics',true
go
EXEC sp_dboption 'Graduate','auto update statistics',true
go
USE Graduate
go
CHECKPOINT
go
IF DB_ID('Graduate') IS NOT NULL
    PRINT '<<< CREATED DATABASE Graduate >>>'
ELSE
    PRINT '<<< FAILED CREATING DATABASE Graduate >>>'
go
-------------------------------------------

CREATE TABLE dbo.Aboutus
(
    id   int      IDENTITY,
    info nvarchar(max) NULL,
    address nvarchar(100) NULL,
    tel nvarchar(10) NULL,
    fax nvarchar(10) NULL,
    CONSTRAINT PK_aboutus
    PRIMARY KEY CLUSTERED (id)
)
go
IF OBJECT_ID('dbo.Aboutus') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.Aboutus >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.Aboutus >>>'
go
---------------------------------------------

CREATE TABLE dbo.Admin
(
    adminName nvarchar(10)    NOT NULL,
    adminPwd  nvarchar(50) NULL,
    lastTime  datetime     NULL,
    lastIp    nvarchar(50) NULL,
    CONSTRAINT PK_admin
    PRIMARY KEY CLUSTERED (adminname)
)
go
IF OBJECT_ID('dbo.Admin') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.Admin >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.Admin >>>'
go
--------------------------------------------

CREATE TABLE dbo.Blogroll
(
    id       int          IDENTITY,
    siteName nvarchar(50) NULL,
    siteLink nvarchar(50) NULL,
    siteLogo nvarchar(50) NULL,
    siteAlt  nvarchar(50) NULL,
    CONSTRAINT PK_blogroll
    PRIMARY KEY CLUSTERED (id)
)
go
IF OBJECT_ID('dbo.Blogroll') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.Blogroll >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.Blogroll >>>'
go
--------------------------------------------

CREATE TABLE dbo.Copyright
(
    id      int          IDENTITY,
    cryear  nvarchar(10)    NOT NULL,
    owner   nvarchar(50) NOT NULL,
    approve nvarchar(50) NOT NULL,
    CONSTRAINT PK_Copyright
    PRIMARY KEY CLUSTERED (id)
)
go
IF OBJECT_ID('dbo.Copyright') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.Copyright >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.Copyright >>>'
go
----------------------------------------------------

CREATE TABLE dbo.CourseType
(
    typeId   int       IDENTITY,
    typeName nvarchar(10) NOT NULL,
    status   char(1)   NOT NULL,
    CONSTRAINT PK_CourseType
    PRIMARY KEY CLUSTERED (TypeID)
)
go
IF OBJECT_ID('dbo.CourseType') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.CourseType >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.CourseType >>>'
go
------------------------------------------------------
CREATE TABLE dbo.Teacher
(
    teacherId   nvarchar(10)    NOT NULL,
    teacherName nvarchar(20) NOT NULL,
    about       nvarchar(max)     NULL,
    picUrl      nvarchar(max)     NULL,
    pwd         nvarchar(max)     NOT NULL,
    lastTime    datetime     NULL,
    lastIP      nvarchar(20) NULL,
    status      char(1)      NOT NULL,
    CONSTRAINT  PK_Teacher
    PRIMARY KEY CLUSTERED (TeacherID)
)
go
IF OBJECT_ID('dbo.Teacher') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.Teacher >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.Teacher >>>'
go
----------------------------------------------------
drop table dbo.[user]
CREATE TABLE dbo.[User]
(
    UserID   int          IDENTITY,
    UserName nvarchar(20) NOT NULL,
    pwd      nvarchar(max)     NOT NULL,
    Gold     float        NOT NULL,
    lastTime datetime     NULL,
    lastIP   nvarchar(50) NULL,
    regTime  datetime     NOT NULL,
    Email    nvarchar(50) NOT NULL,
    Sex      nvarchar(2)     NULL,
    status   char(1)      NOT NULL,
    CONSTRAINT PK_User
    PRIMARY KEY CLUSTERED (UserID)
)
go
IF OBJECT_ID('dbo.User') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.User >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.User >>>'
go


----------------------------------------------

CREATE TABLE dbo.Course
(
    courseId   nvarchar(10) NOT NULL,
    courseHour float     NOT NULL,
    about      nvarchar(max)  NULL,
    teacherId  nvarchar(10) NOT NULL,
    gold       float     NOT NULL,
    courseType int       NOT NULL,
    status     char(1)   NOT NULL,
    CONSTRAINT PK_Course
    PRIMARY KEY CLUSTERED (CourseID),
    CONSTRAINT FK_Course_CourseType
    FOREIGN KEY (CourseType)
    REFERENCES dbo.CourseType (TypeID),
    CONSTRAINT FK_Course_Teacher
    FOREIGN KEY (TeacherID)
    REFERENCES dbo.Teacher (TeacherID)
)
go
IF OBJECT_ID('dbo.Course') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.Course >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.Course >>>'
go
---------------------------------------------

CREATE TABLE dbo.CourseSell
(
    id       int       IDENTITY,
    courseId nvarchar(10) NOT NULL,
    userId   int       NOT NULL,
    [time]   datetime  NOT NULL,
    CONSTRAINT PK_CourseSell
    PRIMARY KEY CLUSTERED (id),
    CONSTRAINT FK_CourseSell_User
    FOREIGN KEY (UserID)
    REFERENCES dbo.[User] (UserID),
    CONSTRAINT FK_CourseSell_Course
    FOREIGN KEY (CourseID)
    REFERENCES dbo.Course (CourseID)
)
go
IF OBJECT_ID('dbo.CourseSell') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.CourseSell >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.CourseSell >>>'
go

----------------------------------------------

CREATE TABLE dbo.Courseware
(
    coursewareId   nvarchar(10) NOT NULL,
    about          nvarchar(max)  NULL,
    courseId       nvarchar(10) NOT NULL,
    uploadTime     datetime  NOT NULL,
    picUrl         nvarchar(max)  NULL,
    coursewareUrl  nvarchar(max)  NOT NULL,
    teacherId      nvarchar(10) NOT NULL,
    status         char(1)   NOT NULL,
    CONSTRAINT PK_Courseware
    PRIMARY KEY CLUSTERED (CoursewareID),
    CONSTRAINT FK_Courseware_Teacher
    FOREIGN KEY (TeacherID)
    REFERENCES dbo.Teacher (TeacherID),
    CONSTRAINT FK_Courseware_Course
    FOREIGN KEY (CourseID)
    REFERENCES dbo.Course (CourseID)
)
go
IF OBJECT_ID('dbo.Courseware') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.Courseware >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.Courseware >>>'
go
--------------------------------------------

CREATE TABLE dbo.CoursewareDown
(
    id           int       IDENTITY,
    CoursewareID nvarchar(10) NULL,
    UserID       int       NULL,
    [time]       datetime  NULL,
    CONSTRAINT PK_CoursewareDown
    PRIMARY KEY CLUSTERED (id),
    CONSTRAINT FK_CoursewareDown_User
    FOREIGN KEY (UserID)
    REFERENCES dbo.[User] (UserID),
    CONSTRAINT FK_CoursewareDown_Courseware
    FOREIGN KEY (CoursewareID)
    REFERENCES dbo.Courseware (CoursewareID)
)
go
IF OBJECT_ID('dbo.CoursewareDown') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.CoursewareDown >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.CoursewareDown >>>'
go
------------------------------------------------------

CREATE TABLE dbo.GoldCard
(
    GoldCardID nvarchar(50) NOT NULL,
    pwd        nvarchar(max)     NOT NULL,
    Gold       float        NOT NULL,
    status     char(1)   NOT NULL,
    CONSTRAINT PK_GoldCard
    PRIMARY KEY CLUSTERED (GoldCardID)
)
go
IF OBJECT_ID('dbo.GoldCard') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.GoldCard >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.GoldCard >>>'
go
------------------------------------------------

CREATE TABLE dbo.GoldSell
(
    id         int          IDENTITY,
    GoldCardID nvarchar(50) NOT NULL,
    [time]     nvarchar(10)    NOT NULL,
    CONSTRAINT PK_GoldSell
    PRIMARY KEY CLUSTERED (id),
    CONSTRAINT FK_GoldSell_GoldCard
    FOREIGN KEY (GoldCardID)
    REFERENCES dbo.GoldCard (GoldCardID)
)
go
IF OBJECT_ID('dbo.GoldSell') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.GoldSell >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.GoldSell >>>'
go
----------------------------------------------------

CREATE TABLE dbo.News
(
    id          int           IDENTITY,
    Title       nvarchar(50)  NULL,
    newsContent nvarchar(max) NULL,
    newsTime    datetime      NULL,
    hit         int           CONSTRAINT DF_News_Hit DEFAULT (0) NULL,
    status      char(1)       NOT NULL DEFAULT 0,
	isPic		char(1)		  NULL,
	PicUrl		nvarchar(max) NULL,
    CONSTRAINT PK_News
    PRIMARY KEY CLUSTERED (id)
)
go
IF OBJECT_ID('dbo.News') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.News >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.News >>>'
go
----------------------------------------------

CREATE TABLE dbo.Slide
(
    id         int          IDENTITY,
    piclink    nvarchar(50) NOT NULL,
    slidetitle nvarchar(50) NOT NULL,
    slidetext  nvarchar(50) NULL,
    slidelink  nvarchar(50) NOT NULL,
    CONSTRAINT PK_Slide
    PRIMARY KEY CLUSTERED (id)
)
go
IF OBJECT_ID('dbo.Slide') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.Slide >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.Slide >>>'
go
------------------------------------------------

CREATE TABLE dbo.Sysdiagrams
(
    name         sysname   NOT NULL,
    principal_id int       NOT NULL,
    diagram_id   int       IDENTITY,
    version      int       NULL,
    definition   varbinary NULL,
    CONSTRAINT PK__sysdiagrams__20C1E124
    PRIMARY KEY CLUSTERED (diagram_id),
    CONSTRAINT UK_principal_name
    UNIQUE NONCLUSTERED (principal_id,name)
)
go
IF OBJECT_ID('dbo.sysdiagrams') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.sysdiagrams >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.sysdiagrams >>>'
go


---------------------------------------------
CREATE TABLE dbo.UserGold
(
    id         int          IDENTITY,
    userId     int          NULL,
    gold       float        NULL,
    goldCardId nvarchar(50) NULL,
    addTime    datetime     NULL,
    CONSTRAINT PK_UserGold
    PRIMARY KEY CLUSTERED (id),
    CONSTRAINT FK_UserGold_User
    FOREIGN KEY (UserID)
    REFERENCES dbo.[User] (UserID),
    CONSTRAINT FK_UserGold_GoldCard
    FOREIGN KEY (GoldCardID)
    REFERENCES dbo.GoldCard (GoldCardID)
)
go
IF OBJECT_ID('dbo.UserGold') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.UserGold >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.UserGold >>>'
go
------------------------------------------------------
CREATE TABLE dbo.Status
(
    statusCode nvarchar(2) NOT NULl,
    statusName nvarchar(10) NOT NULL,
    CONSTRAINT PK_statusCode
    PRIMARY KEY CLUSTERED (statusCode)
)
go
IF OBJECT_ID('dbo.Status') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.Status >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.Status >>>'
go
------------------------------------------------------
